package net.skyedu.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import net.skyedu.bean.AttInfo;
import net.skyedu.bean.FeeDetailByGrade;
import net.skyedu.bean.OtherCheaper;
import net.skyedu.bean.StuFee;
import net.skyedu.bean.StuFeeDetail;
import net.skyedu.bean.StuFeeSecond;
import net.skyedu.bean.Sum;
import net.skyedu.bean.SurplusBalance;

public class AjaxDAO extends DAO{
	private Connection conn = null;
	private PreparedStatement pstmt = null;
	private ResultSet rs = null;
	/**
	 * 连接DB
	 * @param sql
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	private void connection(StringBuffer sql) throws ClassNotFoundException,SQLException {
		conn=this.getConn();
		pstmt=conn.prepareStatement(sql.toString());
		rs=pstmt.executeQuery();
	}
	public int update(int stuid,int courseid,int terid,int deptid,double rollout)
	{
		int num=0;
		StringBuffer sql = new StringBuffer("update edu_studentAccount set rollout=? where studentid=? and periodid=? and courseid=? and deptid=?");
	    String[] params = { Double.toString(rollout), Integer.toString(stuid),Integer.toString(terid),Integer.toString(courseid),Integer.toString(deptid) };
	    try {
            conn = getConn();                              
            pstmt = conn.prepareStatement(sql.toString());    
            if( params != null ) {
                for( int i = 0; i < params.length; i++ ) {
                    pstmt.setString(i+1, params[i]);         
                }
            }
            num = pstmt.executeUpdate();                    
        } catch (ClassNotFoundException e) {
            e.printStackTrace();         
        } catch (SQLException e) {
            e.printStackTrace();                            
        } finally {
            closeAll(conn,pstmt,null);
        }
        return num;
	}
//	public List<Integer> getSumStudent(int terId)
//	{
//		List<Integer> temp=new ArrayList<Integer>();
//		try
//		{
//			StringBuffer sql=new StringBuffer("select distinct StudentId from Edu_StudentAccount where PeriodId="+terId);
//			connection(sql);
//			if(rs != null){
//				while(rs.next()){
//					temp.add((rs.getInt(1)));
////					list.add(obj);
//				}
//			}
//		
//	}catch(Exception e){
//		e.printStackTrace();
//	}finally{
//		this.closeAll(conn, pstmt, rs);
//	}
//	return temp;
////		return null;
//	}
	public List<StuFeeSecond> showlistSecond(int terId,int schId,int subId,
			int graId,String teaName,String stuName,String startTime,String endTime,
			int startIndex,int pagesize,int type,int surplusfee)
	{
//		List<Integer> temp=getSumStudent(terId);
		List<StuFeeSecond> list = new ArrayList<StuFeeSecond>();
		try
		{
//			StringBuffer sql = new StringBuffer("select st.StudentId,st.StudentName,cr.ClassNumber,cf.PeriodId, "+
//					"pr.PeriodYear,pr.PeriodName,dp.DeptName,  "+
//					"sb.SubjectName,gr.GradeName,cr.CourseId,cr.CourseName,tr.TeacherName, "+
//					"cf.RecordTime,cf.UnitFee,cf.Discount, "+
//					"cf.FeeSum,cf.Discount,cf.RecordStaff "+
//					"FROM Edu_StudentAccount cf join Edu_Student st on cf.StudentId=st.StudentId "+
//					"join Edu_Period  pr ON cf.PeriodId = pr.PeriodId "+
//					"join Edu_StudentClass sc on cf.StudentId=sc.StudClassId "+
//					"join Edu_Course cr on cr.CourseId=sc.CourseIdTo "+
//					"join Edu_Grade  gr ON cf.GradeCode = gr.GradeCode "+
//					"join Edu_Subject  sb ON cr.SubjectCode = sb.SubjectCode "+ 
//					"join Edu_Department  dp ON cf.deptid = dp.DeptId  "+
//					"join Edu_Teacher  tr ON cr.TeacherId = tr.TeacherId " );
			//交费课次convert(decimal(10,2),(cf.Coupon+cf.FeeSum+cf.Scholarship+cf.Voucher+CarryOver)/((case when cf.UnitFee=0.0 then 1.0 else cf.UnitFee end)*(case when cf.Discount=0.0 then 1.0 else cf.Discount end))) num
			StringBuffer sql=new StringBuffer("select  distinct cf.deptid, cf.rollout,ec.courseid,ec.coursename,gr.GradeId,sb.SubjectId,cf.classtype,cf.RefundFee,cf.NotPay,cf.CarryOver, cf.booksum,convert(decimal(10,2),(cf.Coupon+cf.Scholarship+cf.Voucher)) othercheaper,convert(decimal(10,2),((case when cf.UnitFee=0.0 then 0.0 else (cf.Coupon+cf.FeeSum+cf.Scholarship+cf.Voucher+CarryOver) end ))/((case when cf.UnitFee=0.0 then 1.0 else cf.UnitFee end)*(case when cf.Discount=0.0 then 1.0 else cf.Discount end))) num,(cf.Coupon+cf.FeeSum+cf.Scholarship+cf.Voucher+CarryOver+cf.NotPay) shouldfee,cf.Balance,st.StudentId,st.StudentName,cf.PeriodId, "+
					"pr.PeriodYear,pr.PeriodName,dp.deptname,cf.gradecode, "+
					"gr.GradeName, sb.SubjectName,"+
					"cf.UnitFee,cf.Discount,"+
					"cf.FeeSum "+
					"FROM Edu_StudentAccount cf join Edu_Student st on cf.StudentId=st.StudentId  "+ 
					"join Edu_Period  pr ON  pr.PeriodId=cf.periodid ");
//					"join (select studentid,SUM(balance) balance from Edu_StudentAccount where ");
			if(surplusfee==1)
			{
				sql.append("where balance>0 ");
			}
			else if(surplusfee==2)
			{
				sql.append("where balance=0 ");
			}
					 
					//"join Edu_StudentClass sc on sc.StudClassId in (select distinct StudClassId from Edu_StudAttendance where studentid="+stuid+" and AttendanceStatus in (1,4,5) and "+ 
				//"CourseId in(select CourseId from Edu_Course where Edu_Course.PeriodId="+terId+ ")) "+
				//	"join Edu_Course cr on cr.CourseId=sc.CourseIdTo "+
					sql.append(" join Edu_Grade  gr ON cf.GradeCode = gr.GradeCode "+
					"join Edu_Subject  sb ON cf.SubjectCode = sb.SubjectCode "+ 
					"join Edu_Course ec on cf.CourseId=ec.CourseId "+
					"join Edu_Department  dp ON cf.deptid = dp.DeptId  " +
					"and cf.status not in (-1) ");
					//"join Edu_Teacher  tr ON cr.TeacherId = tr.TeacherId "+ 
					//"where cf.StudentId="+stuid);
			System.out.println("terId"+terId);
			if(terId != 0){
				sql.append(" and cf.PeriodId = "+terId+" ");
				System.out.println("terId"+terId);
			}
			System.out.println(" schId"+schId);
			if(schId != 0){
				sql.append(" and dp.DeptId ="+schId+" ");
			} 
			System.out.println(" subId"+subId);
			if(subId != 0){
				sql.append(" and sb.SubjectId ="+subId+" ");
			}
			System.out.println(" graId"+graId);
			if(graId != 0){
				sql.append(" and gr.GradeId ="+graId+" ");
			}
//			System.out.println(" teaName"+teaName);
//			if(teaName != null && !"".equals(teaName)){
//				sql.append(" and tr.TeacherName like '%"+teaName+"%' ");
//			}
			System.out.println(" stuName"+stuName);
			if(stuName != null && !"".equals(stuName)){
				sql.append(" and st.StudentName like '%"+stuName+"%' ");
			}
			if(startTime != null && !"".equals(startTime) && endTime != null && !"".equals(endTime)){
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
				  java.util.Date startDate = null;
				  java.util.Date endDate = null;
				  try {
					  startDate = sdf.parse(startTime);
					  endDate = sdf.parse(endTime);
				  } catch (ParseException e) {
					  e.printStackTrace();
				  }
				  java.sql.Date stime = new java.sql.Date(startDate.getTime());//sql类型
				  java.sql.Date etime = new java.sql.Date(endDate.getTime());//sql类型
				sql.append(" and cf.RecordTime between '"+sdf.format(stime)+"' and '"+sdf.format(etime)+"' ");
				
			}
			sql.append("order by  st.StudentId ASC");
			System.out.println(sql);
			connection(sql);
			if(type != 0){
				int i=0;
				if(rs!=null){
					while(rs.next()){
						if((i > (startIndex-1)) && (i < (startIndex + pagesize))){
							StuFeeSecond obj = new StuFeeSecond();
							obj.setSubjectId(rs.getInt("SubjectId"));
							obj.setGradeId(rs.getInt("GradeId"));
							obj.setStuId(rs.getInt("StudentId"));
							obj.setClassType(rs.getInt("classtype"));
//							obj.setClaId(rs.getInt("CourseId"));
							obj.setRefundfee(rs.getDouble("RefundFee"));
							obj.setDeptId(rs.getInt("deptid"));
							obj.setStuName(rs.getString("StudentName"));
							obj.setTerName(rs.getString("PeriodYear")+rs.getString("PeriodName"));
							obj.setSchName(rs.getString("DeptName"));
							obj.setSubName(rs.getString("SubjectName"));
							obj.setGraName(rs.getString("GradeName"));
							obj.setClaNum(rs.getDouble("num"));
							obj.setCarryOver(rs.getDouble("CarryOver"));
							obj.setRollout(rs.getDouble("rollout"));
							obj.setOtherCheaper(rs.getDouble("othercheaper"));
							obj.setNotPay(rs.getDouble("NotPay"));
							obj.setCourseid(rs.getInt("courseid"));
							obj.setCoursename(rs.getString("coursename"));
//							obj.setClaName(rs.getString("CourseName"));
//							obj.setTeaName(rs.getString("TeacherName"));
							obj.setDiscount(rs.getDouble("Discount"));
							obj.setFeeperClass(rs.getDouble("UnitFee"));
							obj.setActualFee(rs.getDouble("FeeSum"));
							obj.setBooksum(rs.getDouble("booksum"));
							if(obj.getStuId()==66)
							{
								System.out.println(rs.getDouble("shouldfee"));
							}
							obj.setShouldFee(rs.getDouble("shouldfee"));
						//	obj.setActualFee(rs.getDouble("ActualCharged"));
//							obj.getUnAcceptFee();
//							obj.setPayType(rs.getInt("PayType"));
//							obj.setPayTime(rs.getString("RecordTime").substring(0,10));
//							obj.setClaNum(rs.getInt("ClassNum"));
//							obj.setReceiver(rs.getString("RecordStaff"));
							obj.setBalance(rs.getDouble("Balance"));
							list.add(obj);
						}
						i++;
					}
				}
			}else{
				if(rs != null){
					while(rs.next()){
						StuFeeSecond obj = new StuFeeSecond();
						obj.setStuId(rs.getInt("StudentId"));
						list.add(obj);
					}
				}
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return list;
	}
	public static void main(String[] args)
	{
		AjaxDAO ad=new AjaxDAO();
//		ad.showlistSecond(35, 0, 0, 0, "", "蔡坤", null, null, 0, 0, 1,2);
	}
	
	/**
	 * 学生费用查询
	 * @param startIndex  索引
	 * @param pagesize  每页要显示的记录数
	 * @param type  类型 0查全部 1查需要的
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
//	String terId,String schId,String subId,String graId,
	public List<StuFee> showlist(int terId,int schId,int subId,
			int graId,String teaName,String stuName,String startTime,String endTime,
			int startIndex,int pagesize,int type) {
		
		List<StuFee> list = new ArrayList<StuFee>();
		try{
			StringBuffer sql = new StringBuffer("select st.StudentId,st.StudentName," +
					"pr.PeriodYear,pr.PeriodName,dp.DeptName," +
					"sb.SubjectName,gr.GradeName,cr.CourseId,cr.CourseName,tr.TeacherName," +
					"cf.PayType,cf.FeeTime,cf.ShouldCharged,cf.Discount," +
					"cf.ActualCharged,cf.Discount,cf.ClassNum,cf.RecordStaff " +
					"FROM Edu_ClassFee cf join Edu_Student st on cf.StudentId=st.StudentId " +
					"join Edu_StudentClass sc on 	cf.StudClassId=sc.StudClassId " +
					"join Edu_Course cr on cr.CourseId=sc.CourseIdTo " +
					"join Edu_Period  pr ON cr.PeriodId = pr.PeriodId " +
					"join Edu_Grade  gr ON cr.GradeCode = gr.GradeCode " +
					"join Edu_Subject  sb ON cr.SubjectCode = sb.SubjectCode " +
					"join Edu_Department  dp ON cr.DeptCode = dp.DeptCode " +
					"join Edu_Teacher  tr ON cr.TeacherId = tr.TeacherId " +
					"where 1=1 ");
			System.out.println("terId"+terId);
			if(terId != 0){
				sql.append("and pr.PeriodId = "+terId+" ");
				System.out.println("terId"+terId);
			}
			System.out.println("schId"+schId);
			if(schId != 0){
				sql.append("and dp.DeptId ="+schId+" ");
			} 
			System.out.println("subId"+subId);
			if(subId != 0){
				sql.append("and sb.SubjectId ="+subId+" ");
			}
			System.out.println("graId"+graId);
			if(graId != 0){
				sql.append("and gr.GradeId ="+graId+" ");
			}
			System.out.println("teaName"+teaName);
			if(teaName != null && !"".equals(teaName)){
				sql.append("and tr.TeacherName like '%"+teaName+"%' ");
			}
			System.out.println("stuName"+stuName);
			if(stuName != null && !"".equals(stuName)){
				sql.append("and st.StudentName like '%"+stuName+"%' ");
			}
			if(startTime != null && !"".equals(startTime) && endTime != null && !"".equals(endTime)){
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
				  java.util.Date startDate = null;
				  java.util.Date endDate = null;
				  try {
					  startDate = sdf.parse(startTime);
					  endDate = sdf.parse(endTime);
				  } catch (ParseException e) {
					  e.printStackTrace();
				  }
				  java.sql.Date stime = new java.sql.Date(startDate.getTime());//sql类型
				  java.sql.Date etime = new java.sql.Date(endDate.getTime());//sql类型
				  
				sql.append("and cf.FeeTime between '"+sdf.format(stime)+"' and '"+sdf.format(etime)+"' ");
			}
		sql.append("order by  st.StudentId ASC");
			System.out.println(sql);
			connection(sql);
			if(type != 0){
				int i=0;
				if(rs!=null){
					while(rs.next()){
						if((i > (startIndex-1)) && (i < (startIndex + pagesize))){
							StuFee obj = new StuFee();
							obj.setStuId(rs.getInt("StudentId"));
							obj.setClaId(rs.getInt("CourseId"));
							obj.setStuName(rs.getString("StudentName"));
							obj.setTerName(rs.getString("PeriodYear")+rs.getString("PeriodName"));
							obj.setSchName(rs.getString("DeptName"));
							obj.setSubName(rs.getString("SubjectName"));
							obj.setGraName(rs.getString("GradeName"));
							obj.setClaName(rs.getString("CourseName"));
							obj.setTeaName(rs.getString("TeacherName"));
							obj.setShouldFee(rs.getDouble("ShouldCharged"));
							obj.setActualFee(rs.getDouble("ActualCharged"));
							obj.setDiscount(rs.getDouble("Discount"));
							obj.setPayType(rs.getInt("PayType"));
							obj.setPayTime(rs.getString("FeeTime").substring(0,10));
							obj.setClaNum(rs.getInt("ClassNum"));
							obj.setReceiver(rs.getString("RecordStaff"));
							list.add(obj);
						}
						i++;
					}
				}
			}else{
				if(rs != null){
					while(rs.next()){
						StuFee obj = new StuFee();
						obj.setStuId(rs.getInt("StudentId"));
						list.add(obj);
					}
				}
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return list;
	}
	
	public List<SurplusBalance> listSurplusBlance(int terId,int stuId,int startIndex,int pagesize,int type,int subClasstype)
	{
		String subName="科学";
		List<SurplusBalance> list=new ArrayList<SurplusBalance>();
		try
		{
			switch(subClasstype)
			{
			case 1:
				subName="英语";
				break;
			case 2:
				subName="数学";
				break;
			case 3:
				subName="语文";
				break;
			default:
				subName="科学";					
			}
			StringBuffer sql=new StringBuffer("select pr.PeriodYear+pr.PeriodName termName,cf.balance,cf.AccountId,st.StudentName "+
					"FROM Edu_StudentAccount cf join Edu_Student st on cf.StudentId=st.StudentId "+
					"join Edu_Period  pr ON  pr.PeriodId=cf.periodid "+
					"join Edu_Grade  gr ON cf.GradeCode = gr.GradeCode "+
//					"join Edu_Subject  sb ON cf.SubjectCode = sb.SubjectCode "+
					"join Edu_Department  dp ON cf.deptid = dp.DeptId "+
					"where  cf.PeriodId<"+terId+" and Balance>0 and cf.StudentId="+stuId+
							"order by cf.StudentId asc");
			System.out.println(sql);
			connection(sql);
			if(type != 0){
				int i=0;
				if(rs!=null){
					while(rs.next()){
						if((i > (startIndex-1)) && (i < (startIndex + pagesize))){
							SurplusBalance obj=new SurplusBalance();
							obj.setAccountId(rs.getInt("AccountId"));
							obj.setTermName(rs.getString("termName"));
//							obj.setSubjectName(rs.getString("SubjectName"));
							obj.setBalance(rs.getDouble("balance"));
							obj.setStuName(rs.getString("studentName"));
							list.add(obj);
						}
						i++;
					}
		}
			}else{
				if(rs != null){
					while(rs.next()){
						SurplusBalance obj = new SurplusBalance();
						obj.setAccountId(rs.getInt("AccountId"));
						list.add(obj);
					}
				}
			}
		
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		this.closeAll(conn, pstmt, rs);
	}
	return list;
	}
	public List<OtherCheaper> listOtherCheaper(int terId,int stuId,int startIndex,int pagesize,int type,int subClasstype)
	{
		String subName="科学";
		List<OtherCheaper> list=new ArrayList<OtherCheaper>();
		try
		{
			switch(subClasstype)
			{
			case 1:
				subName="英语";
				break;
			case 2:
				subName="数学";
				break;
			case 3:
				subName="语文";
				break;
			default:
				subName="科学";					
			}
			StringBuffer sql=new StringBuffer("select cf.AccountId, sb.SubjectName,cf.Voucher,cf.Scholarship,cf.Coupon,st.StudentName "+
					"FROM Edu_StudentAccount cf join Edu_Student st on cf.StudentId=st.StudentId "+
					"join Edu_Period  pr ON  pr.PeriodId=cf.periodid "+
					"join Edu_Grade  gr ON cf.GradeCode = gr.GradeCode "+
					"join Edu_Subject  sb ON cf.SubjectCode = sb.SubjectCode "+ 
					"join Edu_Department  dp ON cf.deptid = dp.DeptId "+
					 "where cf.StudentId="+stuId+" and cf.PeriodId="+terId +"and sb.SubjectName='"+subName+"'");
			
			System.out.println(sql);
			connection(sql);
			if(type != 0){
				int i=0;
				if(rs!=null){
					while(rs.next()){
						if((i > (startIndex-1)) && (i < (startIndex + pagesize))){
							OtherCheaper obj=new OtherCheaper();
							obj.setAccountId(rs.getInt("AccountId"));
							obj.setSubjectName(rs.getString("SubjectName"));
							obj.setCoupon(rs.getDouble("Coupon"));
							obj.setScholarship(rs.getDouble("ScholarShip"));
							obj.setStuName(rs.getString("studentName"));
							obj.setVoucher(rs.getDouble("Voucher"));
							list.add(obj);
						}
						i++;
					}
		}
			}else{
				if(rs != null){
					while(rs.next()){
						OtherCheaper obj = new OtherCheaper();
						obj.setAccountId(rs.getInt("AccountId"));
						list.add(obj);
					}
				}
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return list;
	}
public List<AttInfo> list(int terId,int stuId,int startIndex,int pagesize,int type,String subType,int courseid) {
		
	int temp=Integer.parseInt(subType);
	String subName="";
	switch(temp)
	{
	case 1:
		subName="英语";
		break;
	case 2:
		subName="数学";
		break;
	case 3:
		subName="语文";
		break;
	case 4:
		subName="科学";
		break;
	default:
		subName="科学";
		
	}
		List<AttInfo> list = new ArrayList<AttInfo>();
		try{
			StringBuffer sql = new StringBuffer("select sa.AttendanceId,st.StudentName,cr.CourseName," +
					"sa.ClassTime,dbo.fn_GetClassTime(cr.CourseId) st,sa.AttendanceStatus,tr.TeacherName," +
					"sa.Description " +
					"from Edu_Course cr, Edu_StudAttendance sa,Edu_Student st,Edu_Teacher tr ,Edu_Subject sb " +
					"where cr.CourseId=sa.CourseId " +
					"and sa.StudentId=st.StudentId " +
					"and sa.TeacherId=tr.TeacherId " +
					"and cr.SubjectCode=sb.SubjectCode "+
					"and cr.PeriodId="+terId+" " +
					"and st.StudentId="+stuId+" " +
					"and sb.SubjectName='"+subName+"' "+
					"and cr.courseid="+courseid+
//					and sa.AttendanceStatus<>0
					" order by ClassTime desc");
	System.out.println(sql);
			connection(sql);
			if(type != 0){
				int i=0;
				if(rs!=null){
					while(rs.next()){
						if((i > (startIndex-1)) && (i < (startIndex + pagesize))){
							AttInfo obj = new AttInfo();
							obj.setAttId(rs.getInt("AttendanceId"));
							obj.setStuName(rs.getString("StudentName"));
							obj.setClaName(rs.getString("CourseName"));
							obj.setClaDate(rs.getString("ClassTime").substring(0,10));
							obj.setClaTime(rs.getString("st"));
							obj.setClaState(rs.getInt("AttendanceStatus"));
							obj.setTeaName(rs.getString("TeacherName"));
							obj.setDesc(rs.getString("Description"));
							list.add(obj);
						}
						i++;
					}
				}
			}else{
				if(rs != null){
					while(rs.next()){
						AttInfo obj = new AttInfo();
						obj.setAttId(rs.getInt("AttendanceId"));
						list.add(obj);
					}
				}
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return list;
	}
	public List<AttInfo> list(int terId,int stuId,int claId,int startIndex,int pagesize,int type) {
		
		List<AttInfo> list = new ArrayList<AttInfo>();
		try{
			StringBuffer sql = new StringBuffer("select sa.AttendanceId,st.StudentName,cr.CourseName," +
					"sa.ClassTime,dbo.fn_GetClassTime(cr.CourseId) st,sa.AttendanceStatus,tr.TeacherName," +
					"sa.Description " +
					"from Edu_Course cr, Edu_StudAttendance sa,Edu_Student st,Edu_Teacher tr " +
					"where cr.CourseId=sa.CourseId " +
					"and sa.StudentId=st.StudentId " +
					"and sa.TeacherId=tr.TeacherId " +
					"and cr.PeriodId="+terId+" " +
					"and st.StudentId="+stuId+" " +
					"and cr.CourseId="+claId+" " + 
//					and sa.AttendanceStatus<>0
					"order by ClassTime desc");
//			System.out.println(sql);
			connection(sql);
			if(type != 0){
				int i=0;
				if(rs!=null){
					while(rs.next()){
						if((i > (startIndex-1)) && (i < (startIndex + pagesize))){
							AttInfo obj = new AttInfo();
							obj.setAttId(rs.getInt("AttendanceId"));
							obj.setStuName(rs.getString("StudentName"));
							obj.setClaName(rs.getString("CourseName"));
							obj.setClaDate(rs.getString("ClassTime").substring(0,10));
							obj.setClaTime(rs.getString("st"));
							obj.setClaState(rs.getInt("AttendanceStatus"));
							obj.setTeaName(rs.getString("TeacherName"));
							obj.setDesc(rs.getString("Description"));
							list.add(obj);
						}
						i++;
					}
				}
			}else{
				if(rs != null){
					while(rs.next()){
						AttInfo obj = new AttInfo();
						obj.setAttId(rs.getInt("AttendanceId"));
						list.add(obj);
					}
				}
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return list;
	}
//	select COUNT(*) from Edu_Course cr 
//	INNER JOIN Edu_StudAttendance sa 
//	on cr.CourseId = sa.CourseId
//	where cr.PeriodId=35 
//	and StudentId=15583 
//	and AttendanceStatus in (1,4,5);
	
	
	public double surplusFeeBefore(int terId,int stuId)
	{
		double temp=0.0d;
		try
		{
			StringBuffer sql = new StringBuffer("select sum(Balance) from Edu_StudentAccount ");
			if(stuId != 0)
			{
				sql.append("where  studentid="+stuId+" and periodid<"+terId);
			}
			System.out.println(sql);
			connection(sql);
			while(rs.next()){
				temp = rs.getInt(1);
				}
		}
		catch(Exception e){
		e.printStackTrace();
		}finally{
		this.closeAll(conn, pstmt, rs);
		}
	return temp;
		
		
	}
	public int countsum(int terId,int claId)
	{
		int num=0;
		try
		{
			StringBuffer sql = new StringBuffer("select classnumber from Edu_Course where courseid="+claId);
			System.out.println(sql);
			connection(sql);
			while(rs.next()){
			  num = rs.getInt(1);
			}
		
		}catch(Exception e){
		e.printStackTrace();
		}finally{
		this.closeAll(conn, pstmt, rs);
		}
		return num;
		}
	public int countYiNum(int terId,int stuId,String subjectName, int classtype,int courseid)
	{
		int num=0;
		try
		{
//			StringBuffer sql=new StringBuffer("select count(*) from Edu_StudAttendance where StudentId="+stuId+
//					" and AttendanceStatus in (1,4,5) and CourseId in(select CourseId from Edu_Course where Edu_Course.PeriodId="+terId+" )");
			

			StringBuffer sql=new StringBuffer("select count(*) from Edu_StudAttendance where StudentId="+stuId+
								 " and AttendanceStatus in (1,4,5) and CourseId in(select CourseId from Edu_Course ,edu_subject where Edu_Course.PeriodId=35 and Edu_Course.SubjectCode = Edu_Subject.SubjectCode and Edu_Subject.SubjectName='"+subjectName+"' and Edu_Course.ClassType="+classtype+" and Edu_Course.courseid="+courseid+")");
			System.out.println(sql);
			connection(sql);
			while(rs.next()){
			  num = rs.getInt(1);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return num;
	}
	public int count(int terId,int stuId,int claId){
		int num = 0;
		try{
			StringBuffer sql = new StringBuffer("select COUNT(*) from Edu_Course cr " +
					"INNER JOIN Edu_StudAttendance sa " +
					"on cr.CourseId = sa.CourseId " +
					"where AttendanceStatus in (1,4,5) " +
					"and cr.PeriodId="+terId+" ");
			if(stuId != 0){
				sql.append("and sa.StudentId="+stuId+" ");
			}
			if(claId != 0){
				sql.append("and cr.CourseId="+claId+" ");
			}
			System.out.println(sql);
			connection(sql);
			while(rs.next()){
			  num = rs.getInt(1);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return num;
	}
	//
//	select SUM(cf.ShouldCharged),SUM(cf.ActualCharged),SUM(cf.Discount),SUM(cf.ClassNum)
//	FROM Edu_ClassFee cf join Edu_Student st on cf.StudentId=st.StudentId
//	  join Edu_StudentClass sc on cf.StudClassId=sc.StudClassId
//	  join Edu_Course cr on cr.CourseId=sc.CourseIdTo
//	  JOIN Edu_Period  pr ON cr.PeriodId = pr.PeriodId 
//	  JOIN Edu_Grade  gr ON cr.GradeCode = gr.GradeCode 
//	  JOIN Edu_Subject  sb ON cr.SubjectCode = sb.SubjectCode 
//	  JOIN Edu_Department  dp ON cr.DeptCode = dp.DeptCode 
//	  JOIN Edu_Teacher  tr ON cr.TeacherId = tr.TeacherId 
//	where pr.PeriodId = 35 and dp.DeptId =4 
//	and cf.FeeTime between '2013-03-07 00:00:00' and '2013-03-07 23:59:59' 
//	public static void main(String[] args) {
//		AjaxDAO ad = new AjaxDAO();
//		Sum s = ad.getSum(35, 0, 0, 0, "", "", "", "");
//		System.out.println(s.getSumShould()+"---"+s.getSumActual());
//	}
	
	
	public Sum getSum(int terId,int schId,int subId,
			int graId,String teaName,String stuName,String startTime,String endTime){
		Sum obj = null;
		try{
			StringBuffer sql = new StringBuffer("select SUM(cf.ShouldCharged) sc,SUM(cf.ActualCharged) ac," +
					"SUM(cf.Discount) dc,SUM(cf.ClassNum) cn " +
					"FROM Edu_ClassFee cf join Edu_Student st on cf.StudentId=st.StudentId " +
					"join Edu_StudentClass sc on cf.StudClassId=sc.StudClassId " +
					"join Edu_Course cr on cr.CourseId=sc.CourseIdTo " +
					"JOIN Edu_Period  pr ON cr.PeriodId = pr.PeriodId " +
					"JOIN Edu_Grade  gr ON cr.GradeCode = gr.GradeCode " +
					"JOIN Edu_Subject  sb ON cr.SubjectCode = sb.SubjectCode " +
					"JOIN Edu_Department  dp ON cr.DeptCode = dp.DeptCode " +
					"JOIN Edu_Teacher  tr ON cr.TeacherId = tr.TeacherId " +
					"where 1=1 ");
			if(terId != 0){
				sql.append("and pr.PeriodId = "+terId+" ");
			}
			if(schId != 0){
				sql.append("and dp.DeptId ="+schId+" ");
			} 
			if(subId != 0){
				sql.append("and sb.SubjectId ="+subId+" ");
			}
			if(graId != 0){
				sql.append("and gr.GradeId ="+graId+" ");
			}
			if(teaName != null && !"".equals(teaName)){
				sql.append("and tr.TeacherName like '%"+teaName+"%' ");
			}
			if(stuName != null && !"".equals(stuName)){
				sql.append("and st.StudentName like '%"+stuName+"%' ");
			}
			if(startTime != null && !"".equals(startTime) && endTime != null && !"".equals(endTime)){
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
				  java.util.Date startDate = null;
				  java.util.Date endDate = null;
				  try {
					  startDate = sdf.parse(startTime);
					  endDate = sdf.parse(endTime);
				  } catch (ParseException e) {
					  e.printStackTrace();
				  }
				  java.sql.Date stime = new java.sql.Date(startDate.getTime());//sql类型
				  java.sql.Date etime = new java.sql.Date(endDate.getTime());//sql类型
				  
				sql.append("and cf.FeeTime between '"+sdf.format(stime)+"' and '"+sdf.format(etime)+"' ");
			}
			System.out.println(sql);
			connection(sql);
			while(rs.next()){
			  obj = new Sum();
			  obj.setSumShould(rs.getInt("sc"));
			  obj.setSumActual(rs.getInt("ac"));
			  obj.setSumClaNum(rs.getInt("cn"));
//			  obj.setSumdiscount(rs.getDouble("dc"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return obj;
	}
	
	
//	public static void main(String args[])
//	{
//		AjaxDAO ad=new AjaxDAO();
//		
//	}
	public List<FeeDetailByGrade> getDetailFeeInfo(int schoolID,int gradeID,int subID,String startTime,String endTime)
	{
		List<FeeDetailByGrade> list=new ArrayList<FeeDetailByGrade>();
		FeeDetailByGrade feedetail=null;
		StringBuffer sql=new StringBuffer("select deptname,subjectname,isnull([一年级],0) one, isnull([二年级],0) two,isnull([三年级],0) three,isnull([四年级],0) four,isnull([五年级],0) five,isnull([六年级],0) six,isnull([七年级],0) seven,isnull([八年级],0) eight,isnull([九年级],0) nine,isnull([高一],0) ten,isnull([高二],0) eleven,isnull([高三],0) twelve ,"+
		     	"isnull([一年级],0) +isnull([二年级],0)+isnull([七年级],0)+isnull([八年级],0)+isnull([九年级],0)+isnull([三年级],0)+isnull([四年级],0)+isnull([五年级],0)+isnull([六年级],0)+isnull([高一],0)+isnull([高二],0)+isnull([高三],0) as sumzh "+
		     	 "from(	select * from(select dept.deptname,gra.GradeName,sub.SubjectName ,ActualCharged "+
				"from Edu_ClassFee f "+
				"join Edu_StudentClass sc on f.StudClassId = sc.StudClassId "+
				"join Edu_Course c on sc.CourseIdTo=c.CourseId "+
				"join Edu_Subject sub on c.SubjectCode=sub.SubjectCode "+ 
				"join Edu_Department dept on dept.DeptCode=f.DeptCode "+
				"join Edu_Grade gra on gra.GradeCode=c.GradeCode "+
				"join Edu_Period  pr ON  pr.PeriodId=c.periodid "+
				"where f.FeeStatus>0 "+
				//"and c.PeriodId=35 "+
				" ");
		if(schoolID != 0){
			sql.append("and dept.DeptId ="+schoolID+" ");
		} 
		if(subID != 0){
			sql.append("and sub.SubjectId ="+subID+" ");
		}
		if(startTime!=null&&!"".equals(startTime)&&endTime!=null&&!"".equals(endTime))
		{
			sql.append("and FeeTime between '"+startTime+"' and ' "+endTime+"' ");
		}
		sql.append(" "+ 
				") p "+
				"pivot(sum(p.ActualCharged)  for p.gradename in ([一年级] ,[二年级],[三年级],[四年级],[五年级],[六年级],[七年级],[八年级],[九年级],[高一],[高二],[高三])) as pvt) temp "+
				"order by temp.DeptName ,temp.SubjectName");
		System.out.println(sql);
		
		try
		{
			connection(new StringBuffer(sql));
			if(rs!=null){
				while(rs.next()){
						feedetail=new FeeDetailByGrade();
						feedetail.setSchoolName(rs.getString("deptname"));
						feedetail.setSubjectName(rs.getString("subjectname"));
						feedetail.setOne(Double.toString((rs.getDouble("one"))));
						feedetail.setTwo(Double.toString((rs.getDouble("two"))));
						feedetail.setThree(Double.toString((rs.getDouble("three"))));
						feedetail.setFour(Double.toString((rs.getDouble("four"))));
						feedetail.setFive(Double.toString((rs.getDouble("five"))));
						feedetail.setSix(Double.toString((rs.getDouble("six"))));
						feedetail.setSeven(Double.toString((rs.getDouble("seven"))));
						feedetail.setEight(Double.toString((rs.getDouble("eight"))));
						feedetail.setNine(Double.toString((rs.getDouble("nine"))));
						feedetail.setTen(Double.toString((rs.getDouble("ten"))));
						feedetail.setEleven(Double.toString((rs.getDouble("eleven"))));
						feedetail.setTwelve(Double.toString((rs.getDouble("twelve"))));
						feedetail.setSum(Double.toString(rs.getDouble("sumzh")));
						list.add(feedetail);
				}
			}
		} catch (ClassNotFoundException e) {
		e.printStackTrace();
		} catch (SQLException e) {
		e.printStackTrace();
		}
		finally
		{
			this.closeAll(conn, pstmt, rs);
		}
		
		return list;
	}
	
	/**
	 * 实收费用
	 * @param studentId
	 * @param SubjectCode
	 * @param GradeCode
	 * @param PeriodId
	 * @return
	 */
	public List<StuFeeDetail> ActualFee(int studentId,int SubjectId,int GradeId,int PeriodId,int startIndex,int pagesize,int type,int courseid){
		
		
		List<StuFeeDetail> stufeeList=new ArrayList<StuFeeDetail>();
		StuFeeDetail stufee=null;
		String sql = "select estu.studentname,f.feeid,sub.SubjectName,dept.DeptName,Feetime,ActualCharged as AlreadyFee " +
				"from Edu_ClassFee f " +
				"join Edu_StudentClass sc on f.StudClassId = sc.StudClassId " +
				"join Edu_Course c on sc.CourseIdTo=c.CourseId " +
				"join Edu_Subject sub on c.SubjectCode=sub.SubjectCode " +
				"join Edu_Department dept on dept.DeptCode=f.DeptCode " +
				"join Edu_Grade gra on gra.GradeCode=c.GradeCode "+
				"join Edu_student estu on estu.studentid=f.studentid "+
				"where f.FeeStatus>0 " +
				"and f.StudentId= " +studentId + 
				"and sub.SubjectId = " +SubjectId + 
				"and gra.GradeId= " + GradeId +
				"and c.PeriodId="+PeriodId+
				" and c.courseid= "+courseid;
		try {
			System.out.println(sql);
			connection(new StringBuffer(sql));
			if(type!=0)
			{
			int i=0;
			if(rs!=null){
				while(rs.next()){
					if((i > (startIndex-1)) && (i < (startIndex + pagesize))){
						stufee=new StuFeeDetail();
						stufee.setFeeID(rs.getInt("feeid"));
						stufee.setDeptName(rs.getString("DeptName"));
						stufee.setActualCharged(rs.getDouble("AlreadyFee"));
						stufee.setFeeTime(rs.getString("Feetime"));
						stufee.setSubjectName(rs.getString("SubjectName"));
						stufee.setStudentName(rs.getString("studentname"));
						stufeeList.add(stufee);
					}
					i++;
				}
			}
			}
			else
			{
				if(rs != null){
					while(rs.next()){
						StuFeeDetail obj = new StuFeeDetail();
						obj.setFeeID(rs.getInt("feeid"));
						stufeeList.add(obj);
					}
				}
			}
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return stufeeList;
	}
	
public List<StuFeeDetail> ActualRefundFee(int studentId,int SubjectId,int GradeId,int PeriodId,int startIndex,int pagesize,int type,int courseid){
		
		
		List<StuFeeDetail> stufeeList=new ArrayList<StuFeeDetail>();
		StuFeeDetail stufee=null;
		String sql = " select estu.studentname,er.refundid,sub.SubjectName,dept.DeptName,er.recordTime,er.RefundCash  from Edu_Refund er,Edu_Student estu,Edu_StudentClass sc,Edu_Course c,Edu_Subject sub,Edu_Department dept, Edu_Grade gra "+
   "where er.StudentId=estu.StudentId and sc.StudClassId=er.StudClassId and "+
   "c.SubjectCode=sub.SubjectCode and sc.CourseIdTo=c.CourseId and  dept.DeptCode=er.DeptCode and gra.GradeCode=c.GradeCode "+
   "and er.StudentId= "+studentId+
   " and sub.SubjectId = " +SubjectId+
   " and gra.GradeId=" +GradeId+
   " and c.PeriodId="+PeriodId+
   " and c.courseid="+courseid;
		try {
			System.out.println(sql);
			connection(new StringBuffer(sql));
			if(type!=0)
			{
			int i=0;
			if(rs!=null){
				while(rs.next()){
					if((i > (startIndex-1)) && (i < (startIndex + pagesize))){
						stufee=new StuFeeDetail();
						stufee.setFeeID(rs.getInt("refundid"));
						stufee.setDeptName(rs.getString("DeptName"));
						stufee.setActualCharged(rs.getDouble("RefundCash"));
						stufee.setFeeTime(rs.getString("Feetime"));
						stufee.setSubjectName(rs.getString("SubjectName"));
						stufee.setStudentName(rs.getString("studentname"));
						stufeeList.add(stufee);
					}
					i++;
				}
			}
			}
			else
			{
				if(rs != null){
					while(rs.next()){
						StuFeeDetail obj = new StuFeeDetail();
						obj.setFeeID(rs.getInt("refundid"));
						stufeeList.add(obj);
					}
				}
			}
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return stufeeList;
	
}

public List<StuFeeSecond> ActualFeeList(int studentId,int SubjectId,int GradeId,int PeriodId,int startIndex,int pagesize,int type,int courseid){
	
	
	List<StuFeeSecond> stufeeList=new ArrayList<StuFeeSecond>();
	StuFeeSecond stufee=null;
	String sql = " select c.PeriodId,estu.Studentname,c.SubjectCode,c.GradeCode,c.CourseName, c.UnitClassFee as unitfee, "+
    		"sc.Discount as discount, COUNT(1) as num, "+
    		"SUM(c.UnitClassFee * sc.Discount) as feesum "+
    		"from Edu_StudAttendance sa "+
    		"  join Edu_StudentClass sc on sa.StudClassId = sc.StudClassId "+ 
    		" join Edu_Course c on sa.CourseId = c.CourseId "+ 
    		"join Edu_Subject sub on c.SubjectCode=sub.SubjectCode " +
    		"join Edu_Grade gra on gra.GradeCode=c.GradeCode "+
    		"join Edu_student estu on estu.studentid=sa.studentid "+
			"where c.PeriodId= "+PeriodId+
			"  and sa.AttendanceStatus in (1,4,5) "+
			"and sa.StudentId= "+studentId+
			" and sub.SubjectId = " +SubjectId+
			" and gra.GradeId=" +GradeId+
			" and c.courseid=" +courseid+
			" group by  c.PeriodId,estu.Studentname,sa.StudentId,c.SubjectCode,c.GradeCode,c.CourseName,c.UnitClassFee,  sc.Discount   ";
	try {
		System.out.println(sql);
		connection(new StringBuffer(sql));
		if(type!=0)
		{
		int i=0;
		if(rs!=null){
			while(rs.next()){
				if((i > (startIndex-1)) && (i < (startIndex + pagesize))){
					stufee=new StuFeeSecond();
					stufee.setStuName(rs.getString("Studentname"));
					stufee.setAlreadyFee(rs.getDouble("feesum"));
					stufee.setClaName(rs.getString("CourseName"));
					stufee.setUnitfee(rs.getDouble("unitfee"));
					stufee.setDiscount(rs.getDouble("discount"));
					stufee.setAlreadyClaNum(rs.getInt("num"));
					stufeeList.add(stufee);
				}
				i++;
			}
		}
		}
		else
		{
			if(rs != null){
				while(rs.next()){
					StuFeeSecond obj = new StuFeeSecond();
					obj.setAlreadyFee(rs.getDouble("feesum"));
					stufeeList.add(obj);
				}
			}
		}
		
	} catch (ClassNotFoundException e) {
		e.printStackTrace();
	} catch (SQLException e) {
		e.printStackTrace();
	}
	return stufeeList;

}
}
